using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;
using System.Windows.Forms;
using ctsu.Admin.Forms;
using ctsu.BaseForms;
using ctsu.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;

using Syncfusion.Windows.Forms.Grid;

namespace ctsu.Admin.BusLogic.CoreLab
{

	public class BLCoreLabFormGridLegacyLabels : BLCoreLabFormGridBaseImpl
	{
		public BLCoreLabFormGridLegacyLabels(FormGridMeta formGridMeta, GridControl grid, FormInfoBase formInfo, Form form)
			: base(formGridMeta, grid, formInfo, form)
		{

		}

		#region initialize
		public override bool Initialize()
		{
			this._errorControl = (Label)Forms.Utils.GetControlByName("errCtl", this._form.Controls);
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT t.ID, t.DrawSample, d.Protocol, d.Patient, d.SampleType, " +
				" t.TestType, t.UnitOfMeasure, t.Location, t.Note, t.SmallNote, d.VisitDate, " +
				" d.Visit, d.VisitTime, t.BatchInNum FROM TestSamples t,DrawSamples d" +
				" WHERE 1=2 AND d.ID=t.DrawSample";

			try
			{
				this._tableSchema = Forms.Utils.GetTableSchema(null, sql);

				InitializeGridControl();
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Could not retrieve grid table schema!");
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				//bool b = ////ExceptionPolicy.HandleException(ex, "Exception Policy");
				MessageBox.Show(this._form, sb.ToString(), "Retrieve Grid Data Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return false;
			}
		}

		private void InitializeGridControl()
		{
			this._grid.BeginUpdate();
			//this adds up the total column width
			int totColWidth = 0;

			int col = 1;
			//create the new grid columns for drawSamples			
			this._grid.ColCount = this._formGridMeta.GridColumns.Count;
			//start with drawSamples table
			foreach (DataColumn dc in this._tableSchema.Columns)
			{
				//get the metadata for this column
				if (!this._formGridMeta.GridColumns.Contains(dc.ColumnName))
					continue;

				FormGridColumnMeta fgcm = (FormGridColumnMeta)this._formGridMeta.GridColumns[dc.ColumnName];
				//store the dataColum in the tag property
				//in the header row
				this._grid[0, col].Tag = dc;
				this._grid[0, col].Text = fgcm.DisplayName;
				GridStyleInfo gsi = this._grid.ColStyles[col];
				gsi.CellType = fgcm.CellType;
				gsi.Enabled = true;

				if (fgcm.CellType == "CheckBox")
				{
					gsi.CheckBoxOptions = new GridCheckBoxCellInfo("1", "0", "", true);
					gsi.TriState = false;
					gsi.CellValueType = typeof(string);
					gsi.CellValue = "0";

				}
				else
				{
					gsi.CellValue = null;
					gsi.CellValueType = Type.GetType(fgcm.DataType);

					if (fgcm.CellType == "ComboBox")
					{
						BindGridColumn(fgcm, gsi);
						gsi.DropDownStyle = GridDropDownStyle.AutoComplete;
						gsi.ReadOnly = false;
					}
				}

				this._grid.ColWidths[col] = fgcm.Width;
				totColWidth += fgcm.Width;
				if (fgcm.IsHidden)
					this._grid.Cols.Hidden[col] = true;
				if (fgcm.ReadOnly)
					gsi.ReadOnly = true;
				col++;
			}

			HookGridEvents();

			//			if(totColWidth < MaxWidth)
			//				this._grid.Width = totColWidth + 60;

			this._grid.ResizeRowsBehavior = GridResizeCellsBehavior.None;
			this._grid.Model.RowHeights.ResizeToFit(GridRangeInfo.Row(0), GridResizeToFitOptions.IncludeHeaders);
			this._grid.EndUpdate();

			_btnEditBatch = (Button)Forms.Utils.GetControlByName("btnEditBatch", this._form.Controls);
			_btnRemoveBatch = (Button)Forms.Utils.GetControlByName("btnRemoveBatch", this._form.Controls);
			_btnPrint = (Button)Forms.Utils.GetControlByName("btnPrint", this._form.Controls);
			_btnSave = (Button)Forms.Utils.GetControlByName("btnSave", this._form.Controls);
		}
		#endregion initialize

		#region get display data
		public override bool GetData(int id)
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT t.ID, t.DrawSample, d.Protocol, d.Patient, d.SampleType, " +
				" t.TestType, t.UnitOfMeasure, t.Location, t.Note, t.SmallNote, d.VisitDate, " +
				" d.Visit, d.VisitTime FROM TestSamples t,DrawSamples d" +
				" WHERE t.BatchInNum =" + id +
				" AND d.ID=t.DrawSample " +
				" ORDER BY t.ID";


			//SqlConnection conn = LoginForm.GetSqlConnection();
			try
			{
				DataSet ds = DataAccess.GetDataSet(sql);
				_data = ds.Tables[0];

				this._grid.BeginUpdate();
				this._grid.RowCount = 0;

				this._grid.RowCount = _data.Rows.Count;
				int row = 1;
				foreach (DataRow dr in _data.Rows)
				{
					//store the dataRow in the cell.tag of the header column
					this._grid[row, 0].Tag = dr;
					DisplayRow(row, dr);
					row++;
				}
				this._grid.EndUpdate();
				if (this._grid.RowCount > 0)
					this._btnPrint.Enabled = true;
				else
					this._btnPrint.Enabled = false;


			}
			catch (Exception ex)
			{
				Console.WriteLine(ex.Message);
				//TODO - need to do more here for exception				
				MessageBox.Show("GetGridData Exception");
				return false;
			}
			finally
			{
				//if(conn != null)
				//    conn.Dispose();
			}
			return true;

		}
		#endregion get display data

		#region new replace add rows
		public override void New()
		{
			string sql = "SELECT t.ID, t.DrawSample, d.Protocol, d.Patient, d.SampleType, " +
				" t.TestType, t.UnitOfMeasure, t.Location, t.Note, t.SmallNote, d.VisitDate, " +
				" d.Visit, d.VisitTime, t.BatchInNum FROM TestSamples t,DrawSamples d " +
				" WHERE 1=2 AND d.ID=t.DrawSample";

			//SqlConnection conn = LoginForm.GetSqlConnection();
			StringBuilder sb = new StringBuilder();
			try
			{
				DataSet ds = DataAccess.GetDataSet(sql);
				_data = ds.Tables[0];
				//DataRow dr = _data.NewRow();
				//_data.Rows.Add(dr);
				this._grid.RowCount = 0;
				//this._grid[1,0].Tag = dr;	
			}
			catch (Exception ex)
			{
				sb.Append("Could not retrieve new empty grid table!");
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				//bool b = ////ExceptionPolicy.HandleException(ex, "Exception Policy");
				MessageBox.Show(this._form, sb.ToString(), "Retrieve Grid Table Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
			finally
			{
				//if(conn != null)
				//    conn.Dispose();
			}
		}

		public override void ReplaceRow(object obj, int row)
		{
			DataRow dr = (DataRow)this._grid[row, 0].Tag;
			Sample samp = (Sample)obj;

			if (samp.Protocol > 0)
			{
				this._grid[row, 3].CellValue = samp.Protocol;
				this._grid[row, 3].Tag = samp.ProtocolInfo;
				this._grid[row, 3].CellTipText = samp.ProtocolInfo.GetToolTip();
				AssignChangeToDataRow(row, 3);
			}
			if (samp.PatientID > 0)
			{
				this._grid[row, 4].CellValue = samp.PatientID;
				this._grid[row, 4].Tag = samp.PatientInfo;
				this._grid[row, 4].CellTipText = samp.PatientInfo.GetToolTip();
				AssignChangeToDataRow(row, 4);
			}
			if (samp.SampleType > -1)
			{
				this._grid[row, 5].CellValue = samp.SampleType;
				AssignChangeToDataRow(row, 5);
			}
			if (samp.TestType > -1)
			{
				this._grid[row, 6].CellValue = samp.TestType;
				AssignChangeToDataRow(row, 6);
			}
			if (samp.UnitOfMeasure > -1)
			{
				this._grid[row, 7].CellValue = samp.UnitOfMeasure;
				AssignChangeToDataRow(row, 7);
			}
			if (samp.Location.Length > 0)
			{
				this._grid[row, 8].CellValue = samp.Location;
				AssignChangeToDataRow(row, 8);
			}
			if (samp.Note.Length > 0)
			{
				this._grid[row, 9].CellValue = samp.Note;
				AssignChangeToDataRow(row, 9);
			}
			if (samp.SmallNote.Length > 0)
			{
				this._grid[row, 10].CellValue = samp.SmallNote;
				AssignChangeToDataRow(row, 10);
			}
			if (samp.VisitDate.Ticks > 0)
			{
				this._grid[row, 11].CellValue = samp.VisitDate;
				this._grid[row, 11].Format = "MM/dd/yyyy";
				AssignChangeToDataRow(row, 11);
			}
			if (samp.Visit.Length > 0)
			{
				this._grid[row, 12].CellValue = samp.Visit;
				AssignChangeToDataRow(row, 12);
			}
			if (samp.VisitTime.Length > 0)
			{
				this._grid[row, 13].CellValue = samp.VisitTime;
				AssignChangeToDataRow(row, 13);
			}

			this._btnEditBatch.Enabled = true;
			this._btnRemoveBatch.Enabled = true;
			this._btnSave.Enabled = true;
		}

		public override void AddRow(object obj, int row)
		{
			Sample samp = (Sample)obj;
			DataRow dr = _data.NewRow();
			this._data.Rows.Add(dr);

			int rows = ++this._grid.RowCount;
			this._grid[rows, 0].Tag = dr;
			this._grid[rows, 1].Tag = samp;

			this._grid[rows, 3].CellValue = samp.Protocol;
			this._grid[rows, 3].Tag = samp.ProtocolInfo;
			this._grid[rows, 3].CellTipText = samp.ProtocolInfo.GetToolTip();
			AssignChangeToDataRow(rows, 3);
			this._grid[rows, 4].CellValue = samp.PatientID;
			this._grid[rows, 4].Tag = samp.PatientInfo;
			this._grid[rows, 4].CellTipText = samp.PatientInfo.GetToolTip();
			AssignChangeToDataRow(rows, 4);
			this._grid[rows, 5].CellValue = samp.SampleType;
			AssignChangeToDataRow(rows, 5);
			this._grid[rows, 6].CellValue = samp.TestType;
			AssignChangeToDataRow(rows, 6);
			this._grid[rows, 7].CellValue = samp.UnitOfMeasure;
			AssignChangeToDataRow(rows, 7);
			this._grid[rows, 8].CellValue = samp.Location;
			AssignChangeToDataRow(rows, 8);
			this._grid[rows, 9].Text = samp.Note;
			AssignChangeToDataRow(rows, 9);
			this._grid[rows, 10].Text = samp.SmallNote;
			AssignChangeToDataRow(rows, 10);
			this._grid[rows, 11].CellValue = samp.VisitDate;
			this._grid[rows, 11].Format = "MM/dd/yyyy";
			AssignChangeToDataRow(rows, 11);
			this._grid[rows, 12].CellValue = samp.Visit;
			AssignChangeToDataRow(rows, 12);
			this._grid[rows, 13].CellValue = samp.VisitTime;
			AssignChangeToDataRow(rows, 13);

			this._btnEditBatch.Enabled = true;
			this._btnRemoveBatch.Enabled = true;
			this._btnSave.Enabled = true;
			this._grid.ScrollCellInView(this._grid.RowCount, 0);
		}
		#endregion new replace add rows

		#region save
		public override bool Save(int foreignID, out string message, Database db, DbTransaction trans)
		{
			message = "";
			if (_data == null)
				return true;

			foreach (DataRow dr in _data.Rows)
			{
				if (dr.RowState == DataRowState.Added)
				{
					if (!SaveNewDraw(dr, foreignID, out message, db, trans))
						return false;
					if (!SaveNewTest(dr, foreignID, out message, db, trans))
						return false;
				}
				else if (dr.RowState == DataRowState.Modified)
				{
					if (!UpdateDrawRow(dr, foreignID, out message, db, trans))
						return false;
					if (!UpdateTestRow(dr, foreignID, out message, db, trans))
						return false;
				}
				else if (dr.RowState == DataRowState.Deleted)
				{
					//if(!DeleteRow(dr, out message, trans))
					return true;
				}
			}
			return true;
		}

		private bool UpdateDrawRow(DataRow dr, int foreignID, out string message, Database db, DbTransaction trans)
		{
			message = "";

			//sql update
			string sqlUpdate = "UPDATE DrawSamples Set Protocol=@Protocol, Patient=@Patient, " +
				" VisitDate=@VisitDate, Visit=@Visit, VisitTime=@VisitTime, SampleType=@SampleType " +
				" WHERE ID = @ID";

			//sql parameters
			SqlParameter[] aParams = new SqlParameter[7];
			aParams[0] = new SqlParameter("@Protocol", SqlDbType.Int, 0, "Protocol");
			aParams[1] = new SqlParameter("@Patient", SqlDbType.Int, 0, "Patient");
			aParams[2] = new SqlParameter("@VisitDate", SqlDbType.DateTime, 0, "VisitDate");
			aParams[3] = new SqlParameter("@Visit", SqlDbType.NVarChar, 50, "Visit");
			aParams[4] = new SqlParameter("@VisitTime", SqlDbType.NVarChar, 50, "VisitTime");
			aParams[5] = new SqlParameter("@SampleType", SqlDbType.Int, 0, "SampleType");
			aParams[6] = new SqlParameter("@ID", SqlDbType.Int, 0, "ID");
			//assign parameters
			AssignParameters(aParams, dr, foreignID, 0);
			aParams[6].Value = dr["DrawSample"];

			try
			{
				DbCommand cmd = db.GetSqlStringCommand(sqlUpdate);
				DataAccess.AddParamArrayToDbCommand(cmd, aParams);

				int i = db.ExecuteNonQuery(cmd, trans);
			}
			catch (Exception ex)
			{
				message = ex.Message;
				return false;
			}
			return true;
		}

		private bool UpdateTestRow(DataRow dr, int foreignID, out string message, Database db, DbTransaction trans)
		{
			message = "";

			//sql update
			string sqlUpdate = "UPDATE TestSamples Set DrawSample=@DrawSample, Location=@Location, " +
				" TestType=@TestType, UnitOfMeasure=@UnitOfMeasure, Note=@Note, SmallNote=@SmallNote " +
				" WHERE ID=@ID";

			//sql parameters
			SqlParameter[] aParams = new SqlParameter[7];
			aParams[0] = new SqlParameter("@DrawSample", SqlDbType.Int, 0, "DrawSample");
			aParams[1] = new SqlParameter("@Location", SqlDbType.NVarChar, 50, "Location");
			aParams[2] = new SqlParameter("@TestType", SqlDbType.Int, 0, "TestType");
			aParams[3] = new SqlParameter("@UnitOfMeasure", SqlDbType.Int, 0, "UnitOfMeasure");
			aParams[4] = new SqlParameter("@Note", SqlDbType.NVarChar, 4000, "Note");
			aParams[5] = new SqlParameter("@SmallNote", SqlDbType.NVarChar, 20, "SmallNote");
			aParams[6] = new SqlParameter("@ID", SqlDbType.Int, 0, "ID");

			//assign parameters
			AssignParameters(aParams, dr, foreignID, 0);

			try
			{
				DbCommand cmd = db.GetSqlStringCommand(sqlUpdate);
				DataAccess.AddParamArrayToDbCommand(cmd, aParams);

				int i = db.ExecuteNonQuery(cmd, trans);
			}
			catch (Exception ex)
			{
				message = ex.Message;
				return false;
			}
			return true;
		}

		private bool SaveNewTest(DataRow dr, int foreignID, out string message, Database db, DbTransaction trans)
		{
			message = "";

			//draw table
			string sqlIns = "INSERT INTO TestSamples(DrawSample,Location,TestType,UnitOfMeasure, " +
				" Note, SmallNote, BatchInNum, IsLegacy) " +
				" VALUES(@DrawSample,@Location,@TestType,@UnitOfMeasure, " +
				" @Note,@SmallNote,@BatchInNum,@IsLegacy)";

			//sql parameters
			SqlParameter[] aParams = new SqlParameter[8];
			aParams[0] = new SqlParameter("@DrawSample", SqlDbType.Int, 0, "DrawSample");
			aParams[1] = new SqlParameter("@Location", SqlDbType.NVarChar, 50, "Location");
			aParams[2] = new SqlParameter("@TestType", SqlDbType.Int, 0, "TestType");
			aParams[3] = new SqlParameter("@UnitOfMeasure", SqlDbType.Int, 0, "UnitOfMeasure");
			aParams[4] = new SqlParameter("@Note", SqlDbType.NVarChar, 4000, "Note");
			aParams[5] = new SqlParameter("@SmallNote", SqlDbType.NVarChar, 20, "SmallNote");
			aParams[6] = new SqlParameter("@BatchInNum", SqlDbType.Int, 0, "BatchInNum");
			aParams[7] = new SqlParameter("@IsLegacy", SqlDbType.Int, 0, "IsLegacy");

			//assign parameters
			AssignParameters(aParams, dr, foreignID, 0);
			aParams[7].Value = 1;

			try
			{
				DbCommand cmd = db.GetSqlStringCommand(sqlIns);
				DataAccess.AddParamArrayToDbCommand(cmd, aParams);

				int i = db.ExecuteNonQuery(cmd, trans);

				//get key id for new row
				//this will assign the new value to the dataRow column
				SetTestKeyValues(db, trans, dr);
			}
			catch (Exception ex)
			{
				message = ex.Message;
				return false;
			}
			return true;
		}

		private bool SaveNewDraw(DataRow dr, int foreignID, out string message, Database db, DbTransaction trans)
		{
			message = "";

			//draw table
			string sqlIns = "INSERT INTO DrawSamples(Protocol,Patient,VisitDate,Visit,VisitTime,SampleType,BatchInNum) " +
				" VALUES(@Protocol,@Patient,@VisitDate,@Visit,@VisitTime,@SampleType,@BatchInNum)";

			//sql parameters
			SqlParameter[] aParams = new SqlParameter[7];
			aParams[0] = new SqlParameter("@Protocol", SqlDbType.Int, 0, "Protocol");
			aParams[1] = new SqlParameter("@Patient", SqlDbType.Int, 0, "Patient");
			aParams[2] = new SqlParameter("@VisitDate", SqlDbType.DateTime, 0, "VisitDate");
			aParams[3] = new SqlParameter("@Visit", SqlDbType.NVarChar, 50, "Visit");
			aParams[4] = new SqlParameter("@VisitTime", SqlDbType.NVarChar, 50, "VisitTime");
			aParams[5] = new SqlParameter("@SampleType", SqlDbType.Int, 0, "SampleType");
			aParams[6] = new SqlParameter("@BatchInNum", SqlDbType.Int, 0, "BatchInNum");

			//assign parameters
			AssignParameters(aParams, dr, foreignID, 0);
			aParams[6].Value = 0;

			try
			{
				DbCommand cmd = db.GetSqlStringCommand(sqlIns);
				DataAccess.AddParamArrayToDbCommand(cmd, aParams);

				int i = db.ExecuteNonQuery(cmd, trans);
				//get key id for new row
				//this will assign the new value to the dataRow column
				SetDrawKeyValues(db, trans, dr);
			}
			catch (Exception ex)
			{
				message = ex.Message;
				return false;
			}
			return true;
		}

		private void SetDrawKeyValues(Database db, DbTransaction trans, DataRow dr)
		{
			//TODO - this is setup for autoIncrement keys only
			//need to something if otherwise		

			try
			{
				object o = db.ExecuteScalar(trans, CommandType.Text, "SELECT @@IDENTITY");
				dr["DrawSample"] = o;
				return;

			}
			catch
			{
			}
		}

		private void SetTestKeyValues(Database db, DbTransaction trans, DataRow dr)
		{
			//TODO - this is setup for autoIncrement keys only
			//need to something if otherwise			

			try
			{
				object o = db.ExecuteScalar(trans, CommandType.Text, "SELECT @@IDENTITY");
				dr["ID"] = o;
				return;

			}
			catch
			{
			}
		}

		#endregion save

		protected override void Grid_CurrentCellChanged(object sender, EventArgs e)
		{
			base.Grid_CurrentCellChanged(sender, e);
			string message = "";
			GridCurrentCell cc = this._grid.CurrentCell;
			//protocol
			if (cc.ColIndex == 3)
			{
				int id = (int)cc.Renderer.ControlValue;
				ProtocolInfo pi = CoreLabUtils.GetProtocolInfo(id, out message);
				this._grid[cc.RowIndex, 3].Tag = pi;
				this._grid[cc.RowIndex, 3].CellTipText = pi.GetToolTip();
			}
			//protocol
			if (cc.ColIndex == 4)
			{
				int id = (int)cc.Renderer.ControlValue;
				PatientInfo pi = CoreLabUtils.GetPatientInfo(id, out message);
				this._grid[cc.RowIndex, 4].Tag = pi;
				this._grid[cc.RowIndex, 4].CellTipText = pi.GetToolTip();
			}
		}

	}
}
